/**
 * NPC基础数据导入脚本
 * 功能：
 * 1) 读取 .env（如存在）与默认配置，连接远程 MySQL 8.0
 * 2) 创建NPC集群表和详情表（如不存在）
 * 3) 读取 ./output/websocket_data_200_npcV2.json，处理NPC数据格式
 * 4) 批量导入（存在则更新）
 */

const fs = require('fs');
const path = require('path');
require('dotenv').config();
const mysql = require('mysql2/promise');

// 数据库配置
const dbConf = {
  host: process.env.DB_HOST || '43.143.253.188',
  port: parseInt(process.env.DB_PORT || '3306'),
  user: process.env.DB_USER || 'nextjs',
  password: process.env.DB_PASSWORD || 'yEAiRE2jcxTyRBy5',
  database: process.env.DB_NAME || 'nextjs',
  charset: 'utf8mb4',
  multipleStatements: true
};

const INPUT_FILE = path.join(__dirname, 'output', 'websocket_data_200_npcV2.json');

// NPC类型映射
const NPC_TYPE_MAP = {
  1: '怪物群',
  2: 'NPC'
};

function safeInt (v) {
  if (v === undefined || v === null) return null;
  const n = parseInt(String(v).replace(/[^0-9-]/g, ''), 10);
  return Number.isNaN(n) ? null : n;
}

// 从地区名称中提取等级信息
function extractLevelFromAreaName (areaName) {
  const match = areaName.match(/\((\d+)\s*级\)/);
  return match ? parseInt(match[1]) : null;
}

// 从地区名称中提取地区名称（去除等级信息）
function extractAreaName (areaName) {
  return areaName.replace(/\s*\(\d+\s*级\)\s*$/, '').trim();
}

async function ensureTables (conn) {
  // 先删除旧表（如果存在）
  await conn.execute('DROP TABLE IF EXISTS npc_details');
  await conn.execute('DROP TABLE IF EXISTS npc_clusters');

  // 创建NPC集群表
  const clusterTableSQL = `
        CREATE TABLE npc_clusters (
            id INT NOT NULL PRIMARY KEY COMMENT 'NPC集群ID',
            name VARCHAR(100) NOT NULL COMMENT '集群名称',
            area_name VARCHAR(100) NOT NULL COMMENT '所属地区名称',
            area_level INT NULL COMMENT '地区等级',
            type_id INT NOT NULL COMMENT '集群类型ID (1:怪物群, 2:NPC)',
            type_name VARCHAR(50) NULL COMMENT '集群类型名称',
            attack_power INT DEFAULT 0 COMMENT '攻击力',
            is_active TINYINT DEFAULT 1 COMMENT '是否激活 (1:激活, 0:禁用)',
            raw_json JSON NULL COMMENT '原始数据',
            created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
            updated_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '更新时间',
            INDEX idx_area_name (area_name),
            INDEX idx_type_id (type_id),
            INDEX idx_is_active (is_active)
        ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci COMMENT='NPC集群表';
    `;

  // 创建NPC详情表
  const detailTableSQL = `
        CREATE TABLE npc_details (
            id INT AUTO_INCREMENT PRIMARY KEY COMMENT 'NPC详情ID',
            cluster_id INT NOT NULL COMMENT '所属集群ID',
            name VARCHAR(100) NOT NULL COMMENT 'NPC名称',
            type_id INT NOT NULL COMMENT 'NPC类型ID',
            type_name VARCHAR(50) NULL COMMENT 'NPC类型名称',
            attack_power INT DEFAULT 0 COMMENT '攻击力',
            is_active TINYINT DEFAULT 1 COMMENT '是否激活 (1:激活, 0:禁用)',
            raw_json JSON NULL COMMENT '原始数据',
            created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
            updated_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '更新时间',
            INDEX idx_cluster_id (cluster_id),
            INDEX idx_type_id (type_id),
            INDEX idx_is_active (is_active),
            FOREIGN KEY (cluster_id) REFERENCES npc_clusters(id) ON DELETE CASCADE
        ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci COMMENT='NPC详情表';
    `;

  await conn.execute(clusterTableSQL);
  await conn.execute(detailTableSQL);
}

async function importData () {
  if (!fs.existsSync(INPUT_FILE)) {
    console.error('未找到数据文件:', INPUT_FILE);
    process.exit(1);
  }

  const rawObj = JSON.parse(fs.readFileSync(INPUT_FILE, 'utf8'));

  const conn = await mysql.createConnection(dbConf);
  try {
    console.log('✅ 已连接 MySQL:', `${dbConf.user}@${dbConf.host}:${dbConf.port}/${dbConf.database}`);
    await ensureTables(conn);

    const clusterUpsertSql = `
            INSERT INTO npc_clusters
                (id, name, area_name, area_level, type_id, type_name, attack_power, raw_json)
            VALUES
                (?, ?, ?, ?, ?, ?, ?, CAST(? AS JSON))
            ON DUPLICATE KEY UPDATE
                name=VALUES(name),
                area_name=VALUES(area_name),
                area_level=VALUES(area_level),
                type_id=VALUES(type_id),
                type_name=VALUES(type_name),
                attack_power=VALUES(attack_power),
                raw_json=VALUES(raw_json),
                updated_at=CURRENT_TIMESTAMP
        `;

    const detailUpsertSql = `
            INSERT INTO npc_details
                (cluster_id, name, type_id, type_name, attack_power, raw_json)
            VALUES
                (?, ?, ?, ?, ?, CAST(? AS JSON))
            ON DUPLICATE KEY UPDATE
                name=VALUES(name),
                type_id=VALUES(type_id),
                type_name=VALUES(type_name),
                attack_power=VALUES(attack_power),
                raw_json=VALUES(raw_json),
                updated_at=CURRENT_TIMESTAMP
        `;

    let clusterCount = 0;
    let detailCount = 0;

    for (const [areaName, npcList] of Object.entries(rawObj)) {
      if (!Array.isArray(npcList)) continue;

      const cleanAreaName = extractAreaName(areaName);
      const areaLevel = extractLevelFromAreaName(areaName);

      // 处理每个NPC
      for (const npc of npcList) {
        if (!npc || typeof npc !== 'object') continue;

        const id = safeInt(npc.id);
        const name = npc.name;
        const typeId = safeInt(npc.typeId);
        const attackPower = safeInt(npc.ap);
        const typeName = NPC_TYPE_MAP[typeId] || '未知';

        if (!id || !name || !typeId) continue;

        // 插入集群数据
        const clusterRawJson = JSON.stringify({
          area: areaName,
          npc: npc
        });

        await conn.execute(clusterUpsertSql, [
          id,
          name,
          cleanAreaName,
          areaLevel,
          typeId,
          typeName,
          attackPower,
          clusterRawJson
        ]);
        clusterCount++;

        // 插入详情数据
        const detailRawJson = JSON.stringify(npc);
        await conn.execute(detailUpsertSql, [
          id,
          name,
          typeId,
          typeName,
          attackPower,
          detailRawJson
        ]);
        detailCount++;
      }
    }

    console.log(`✅ 导入完成，共处理 ${clusterCount} 个NPC集群，${detailCount} 个NPC详情`);

    // 显示统计信息
    const [clusterStats] = await conn.execute(`
            SELECT 
                type_name,
                COUNT(*) as count,
                AVG(area_level) as avg_level,
                MIN(area_level) as min_level,
                MAX(area_level) as max_level
            FROM npc_clusters 
            GROUP BY type_id, type_name
            ORDER BY type_id
        `);

    console.log('\n📊 NPC集群统计:');
    console.table(clusterStats.map(row => ({
      '类型名称': row.type_name,
      '数量': row.count,
      '平均等级': Math.round(row.avg_level || 0),
      '等级范围': `${row.min_level || 0}-${row.max_level || 0}`
    })));

    // 按地区统计
    const [areaStats] = await conn.execute(`
            SELECT 
                area_name,
                area_level,
                COUNT(*) as npc_count,
                GROUP_CONCAT(DISTINCT type_name ORDER BY type_name SEPARATOR ', ') as types
            FROM npc_clusters 
            GROUP BY area_name, area_level
            ORDER BY area_level, area_name
            LIMIT 15
        `);

    console.log('\n📊 按地区统计 (前15个):');
    console.table(areaStats.map(row => ({
      '地区名称': row.area_name,
      '地区等级': row.area_level || '未知',
      'NPC数量': row.npc_count,
      'NPC类型': row.types
    })));

  } finally {
    await conn.end();
  }
}

if (require.main === module) {
  importData().catch((err) => {
    console.error('导入失败:', err.message);
    process.exit(1);
  });
}

module.exports = { importData };

